/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mytools;

import java.sql.*;
import java.util.*;

/**
 *
 * @author wl
 */
public class choiceDAO {

    private Connection con;

    public choiceDAO() throws Exception {
    }

    public List<String> getchoicesubject() throws Exception {
        con = dbcon.getConnection();
        List<String> list = new ArrayList<String>();
        PreparedStatement ps = con.prepareStatement("select distinct subject from choiceques");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("subject"));
        }
        Collections.sort(list);
        con.close();
        return list;

    }

    public List<String> getchoicechapter() throws Exception {
        con = dbcon.getConnection();
        List<String> list = new ArrayList<String>();
        PreparedStatement ps = con.prepareStatement("select distinct chapter from choiceques");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("chapter"));
        }
        Collections.sort(list);
        con.close();
        return list;
    }

    public int getsctlchoiceAmount(String subject, String chapter, String type, String level) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from choiceques where subject like('%" + subject + "%') and chapter like('%" + chapter + "%') and type like('%" + type + "%') and level like('%" + level + "%')";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    public List<choice> getsctlchoicelist(int pagenumber, int pagesize, String subject, String chapter, String type, String level) throws SQLException, Exception {
        con = dbcon.getConnection();
        List<choice> list = new ArrayList<choice>();
        String sql = "select * from choiceques where subject like('%" + subject + "%') and chapter like('%" + chapter + "%') and type like('%" + type + "%') and level like('%" + level + "%')  ORDER BY id DESC limit ?,? ";
        PreparedStatement ps = con.prepareStatement(sql);
        if (pagenumber < 1) {
            pagenumber = 1;
        }
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            choice choice = new choice();
            choice.setId(rs.getInt("id"));
            choice.setName(rs.getString("name"));
            choice.setA(rs.getString("a"));
            choice.setB(rs.getString("b"));
            choice.setC(rs.getString("c"));
            choice.setD(rs.getString("d"));
            choice.setAnswer(rs.getString("answer"));
            choice.setType(rs.getString("type"));
            choice.setLevel(rs.getString("level"));
            choice.setSubject(rs.getString("subject"));
            choice.setChapter(rs.getString("chapter"));
            choice.setChapterinfo(rs.getString("chapterinfo"));
            list.add(choice);
        }
        rs.close();
        con.close();
        return list;
    }

    public choice getchoice(int id) throws SQLException, Exception {
        con = dbcon.getConnection();
        choice choice = null;
        String sql = "select * from choiceques where id=?";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setInt(1, id);
        ResultSet rs = psmt.executeQuery();
        if (rs.next()) {
            choice = new choice();
            choice.setId(id);
            choice.setName(rs.getString("name"));
            choice.setA(rs.getString("a"));
            choice.setB(rs.getString("b"));
            choice.setC(rs.getString("c"));
            choice.setD(rs.getString("d"));
            choice.setAnswer(rs.getString("answer"));
            choice.setChapter(rs.getString("chapter"));
            choice.setChapterinfo(rs.getString("chapterinfo"));
            choice.setLevel(rs.getString("level"));
            choice.setSubject(rs.getString("subject"));
            choice.setType(rs.getString("type"));
        }
        con.close();
        return choice;
    }

    public int deletechoice(int id) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        Statement st = con.createStatement();
        i = st.executeUpdate("delete from choiceques where id='" + id + "'");
        con.close();
        return i;
    }

    public int editchoice(choice choice) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "update choiceques set name=?,a=?,b=?,c=?,d=?,answer=?,type=?,level=?,subject=?,chapter=?,chapterinfo=? where id=?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ps.setString(1, choice.getName());
        ps.setString(2, choice.getA());
        ps.setString(3, choice.getB());
        ps.setString(4, choice.getC());
        ps.setString(5, choice.getD());
        ps.setString(6, choice.getAnswer());
        ps.setString(7, choice.getType());
        ps.setString(8, choice.getLevel());
        ps.setString(9, choice.getSubject());
        ps.setString(10, choice.getChapter());
        ps.setString(11, choice.getChapterinfo());
        ps.setInt(12, choice.getId());
        i = ps.executeUpdate();
        con.close();
        return i;
    }

    public int addchoice(choice choice) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "insert into choiceques value(null,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setString(1, choice.getName());
        psmt.setString(2, choice.getA());
        psmt.setString(3, choice.getB());
        psmt.setString(4, choice.getC());
        psmt.setString(5, choice.getD());
        psmt.setString(6, choice.getAnswer());
        psmt.setString(7, choice.getType());
        psmt.setString(8, choice.getLevel());
        psmt.setString(9, choice.getSubject());
        psmt.setString(10, choice.getChapter());
        psmt.setString(11, choice.getChapterinfo());
        i = psmt.executeUpdate();
        con.close();
        return i;

    }

    public int getchoiceAmount() throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from choiceques";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    public List<choice> getchoicelist(int pagenumber, int pagesize) throws SQLException, Exception {
        con = dbcon.getConnection();
        List<choice> list = new ArrayList<choice>();
        String sql = "select * from choiceques limit ?,?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        if (pagenumber < 1) {
            pagenumber = 1;
        }
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            choice choice = new choice();
            choice.setId(rs.getInt("id"));
            choice.setName(rs.getString("name"));
            choice.setA(rs.getString("a"));
            choice.setB(rs.getString("b"));
            choice.setC(rs.getString("c"));
            choice.setD(rs.getString("d"));
            choice.setAnswer(rs.getString("answer"));
            choice.setType(rs.getString("type"));
            choice.setLevel(rs.getString("level"));
            choice.setSubject(rs.getString("subject"));
            choice.setChapter(rs.getString("chapter"));
            choice.setChapterinfo(rs.getString("chapterinfo"));
            list.add(choice);
        }
        rs.close();
        con.close();
        return list;
    }

    public List<choice> choicelist() throws SQLException, Exception {
        con = dbcon.getConnection();
        List<choice> list = new ArrayList<choice>();
        String sql = "select * from choiceques";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            choice choice = new choice();
            choice.setId(rs.getInt("id"));
            choice.setName(rs.getString("name"));
            choice.setA(rs.getString("a"));
            choice.setB(rs.getString("b"));
            choice.setC(rs.getString("c"));
            choice.setD(rs.getString("d"));
            choice.setAnswer(rs.getString("answer"));
            choice.setType(rs.getString("type"));
            choice.setLevel(rs.getString("level"));
            choice.setSubject(rs.getString("subject"));
            choice.setChapter(rs.getString("chapter"));
            choice.setChapterinfo(rs.getString("chapterinfo"));
            list.add(choice);
        }
        rs.close();
        con.close();
        return list;
    }
}
